<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>06 【多表查询】 | Bin</title>
    <meta name="description" content="帝彬">
    <link rel="stylesheet" href="/assets/style.9f8bf5c4.css">
    <link rel="modulepreload" href="/assets/app.82d46cfc.js">
    <link rel="modulepreload" href="/assets/mysql_06.md.4f4b1170.lean.js">
    <link rel="prefetch" href="/assets/chunks/VPAlgoliaSearchBox.6ebb7441.js">
    <link rel="icon" href="/logo.svg">
  <link rel="preload" href="/assets/inter-latin.7b37fe23.woff2" as="font" type="font/woff2" crossorigin="anonymous">
  <script>(()=>{const e=localStorage.getItem("vue-theme-appearance");(!e||e==="auto"?window.matchMedia("(prefers-color-scheme: dark)").matches:e==="dark")&&document.documentElement.classList.add("dark")})();</script>
  <meta name="generator" content="qq1974892005">
  <link rel="icon" href="/images/logo.svg">
  <meta name="name" content="Bin">
  <script src="https://cdn.jsdelivr.net/npm/clipboard@2.0.6/dist/clipboard.min.js"></script>
  <script src="https://hm.baidu.com/hm.js?d186385b8ee12dbef7031da2e9c9eee3"></script>
  <script src="/public/tinymce/tinymce.js"></script>
  <meta name="twitter:title" content="06 【多表查询】 | Bin">
  <meta property="og:title" content="06 【多表查询】 | Bin">
  </head>
  <body>
    <div id="app"><div class="VPApp" data-v-23306c50><!--[--><span tabindex="-1" data-v-3785d3a7></span><a href="#VPContent" class="VPSkipLink visually-hidden" data-v-3785d3a7> Skip to content </a><!--]--><!----><!--[--><div></div><!--]--><header class="VPNav nav-bar" data-v-23306c50 data-v-7d674ffc><div class="VPNavBar" data-v-7d674ffc data-v-96a69ce8><div class="container" data-v-96a69ce8><a class="VPNavBarTitle" href="/" data-v-96a69ce8 data-v-1f5e00a8><!--[--><img class="logo" src="/logo.svg" alt="bin" srcset="" data-v-1f5e00a8><span class="text" data-v-1f5e00a8>Bin</span><!--]--></a><div class="content" data-v-96a69ce8><!----><nav aria-labelledby="main-nav-aria-label" class="VPNavBarMenu menu" data-v-96a69ce8 data-v-9072bcde><span id="main-nav-aria-label" class="visually-hidden" data-v-9072bcde>Main Navigation</span><!--[--><!--[--><a class="vt-link link VPNavBarMenuLink" href="/yuanshen/" data-v-9072bcde data-v-6bdae1a3><!--[-->原神大地图<!--]--><!----></a><!--]--><!--[--><div class="vt-flyout VPNavBarMenuGroup" data-v-9072bcde data-v-42573883><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false"><span class="vt-flyout-button-text">前端 <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-text-icon"><path d="M12,16c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l5.3,5.3l5.3-5.3c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-6,6C12.5,15.9,12.3,16,12,16z"></path></svg></span></button><div class="vt-flyout-menu"><div class="vt-menu"><div class="vt-menu-items"><!--[--><!--[--><a class="vt-link link vt-menu-link" href="/js/01.html"><!--[-->JavaScript<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/ajax/01.html"><!--[-->ajax<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/ES6/01.html"><!--[-->ES6<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/echarts/01.html"><!--[-->echarts<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/typescript/01.html"><!--[-->typescript<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/vue2/01.html"><!--[-->vue2<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/vue3/01.html"><!--[-->vue3<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/git/01.html"><!--[-->git<!--]--><!----></a><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--[--><div class="vt-flyout VPNavBarMenuGroup" data-v-9072bcde data-v-42573883><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false"><span class="vt-flyout-button-text">服务端 <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-text-icon"><path d="M12,16c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l5.3,5.3l5.3-5.3c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-6,6C12.5,15.9,12.3,16,12,16z"></path></svg></span></button><div class="vt-flyout-menu"><div class="vt-menu"><div class="vt-menu-items"><!--[--><!--[--><a class="vt-link link vt-menu-link" href="/nodejs/01.html"><!--[-->nodejs<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/mysql/01.html"><!--[-->mysql<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/mongodb/01.html"><!--[-->mongodb<!--]--><!----></a><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--[--><div class="vt-flyout VPNavBarMenuGroup" data-v-9072bcde data-v-42573883><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false"><span class="vt-flyout-button-text">CSS提升 <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-text-icon"><path d="M12,16c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l5.3,5.3l5.3-5.3c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-6,6C12.5,15.9,12.3,16,12,16z"></path></svg></span></button><div class="vt-flyout-menu"><div class="vt-menu"><div class="vt-menu-items"><!--[--><!--[--><a class="vt-link link vt-menu-link" href="/less/01.html"><!--[-->less<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/sass/01.html"><!--[-->sass<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/tailwindcss/01.html"><!--[-->tailwindcss<!--]--><!----></a><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--[--><div class="vt-flyout VPNavBarMenuGroup" data-v-9072bcde data-v-42573883><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false"><span class="vt-flyout-button-text">集合 <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-text-icon"><path d="M12,16c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l5.3,5.3l5.3-5.3c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-6,6C12.5,15.9,12.3,16,12,16z"></path></svg></span></button><div class="vt-flyout-menu"><div class="vt-menu"><div class="vt-menu-items"><!--[--><!--[--><a class="vt-link link vt-menu-link" href="/jihe/React/"><!--[-->React<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/jihe/ReactNative/"><!--[-->ReactNative<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/jihe/vue/"><!--[-->Vue<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/jihe/vuex/"><!--[-->Vuex<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/jihe/vuerouter/"><!--[-->Vue-Router<!--]--><!----></a><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--[--><div class="vt-flyout VPNavBarMenuGroup" data-v-9072bcde data-v-42573883><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false"><span class="vt-flyout-button-text">参数 <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-text-icon"><path d="M12,16c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l5.3,5.3l5.3-5.3c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-6,6C12.5,15.9,12.3,16,12,16z"></path></svg></span></button><div class="vt-flyout-menu"><div class="vt-menu"><div class="vt-menu-items"><!--[--><!--[--><a class="vt-link link vt-menu-link" href="/about/canshu.html"><!--[-->常用参数<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/about/dayin.html"><!--[-->打印机<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/about/chahuo.html"><!--[-->市场查货<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="https://sunlogin.oray.com/download" target="_blank" rel="noopener noreferrer"><!--[-->向日葵远程下载<!--]--><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" height="24px" viewbox="0 0 24 24" width="24px" class="vt-link-icon"><path d="M0 0h24v24H0V0z" fill="none"></path><path d="M9 5v2h6.59L4 18.59 5.41 20 17 8.41V15h2V5H9z"></path></svg></a><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--[--><div class="vt-flyout VPNavBarMenuGroup" data-v-9072bcde data-v-42573883><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false"><span class="vt-flyout-button-text">其他 <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-text-icon"><path d="M12,16c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l5.3,5.3l5.3-5.3c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-6,6C12.5,15.9,12.3,16,12,16z"></path></svg></span></button><div class="vt-flyout-menu"><div class="vt-menu"><div class="vt-menu-items"><!--[--><!--[--><a class="vt-link link vt-menu-link" href="/test/"><!--[-->日志<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/test/muban.html"><!--[-->富文本模板<!--]--><!----></a><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--]--></nav><div class="VPNavBarAppearance appearance" data-v-96a69ce8 data-v-d5f66188><button class="vt-switch vt-switch-appearance" type="button" role="switch" aria-label="toggle dark mode" data-v-d5f66188><span class="vt-switch-check"><span class="vt-switch-icon"><!--[--><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-switch-appearance-sun"><path d="M12,18c-3.3,0-6-2.7-6-6s2.7-6,6-6s6,2.7,6,6S15.3,18,12,18zM12,8c-2.2,0-4,1.8-4,4c0,2.2,1.8,4,4,4c2.2,0,4-1.8,4-4C16,9.8,14.2,8,12,8z"></path><path d="M12,4c-0.6,0-1-0.4-1-1V1c0-0.6,0.4-1,1-1s1,0.4,1,1v2C13,3.6,12.6,4,12,4z"></path><path d="M12,24c-0.6,0-1-0.4-1-1v-2c0-0.6,0.4-1,1-1s1,0.4,1,1v2C13,23.6,12.6,24,12,24z"></path><path d="M5.6,6.6c-0.3,0-0.5-0.1-0.7-0.3L3.5,4.9c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l1.4,1.4c0.4,0.4,0.4,1,0,1.4C6.2,6.5,5.9,6.6,5.6,6.6z"></path><path d="M19.8,20.8c-0.3,0-0.5-0.1-0.7-0.3l-1.4-1.4c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l1.4,1.4c0.4,0.4,0.4,1,0,1.4C20.3,20.7,20,20.8,19.8,20.8z"></path><path d="M3,13H1c-0.6,0-1-0.4-1-1s0.4-1,1-1h2c0.6,0,1,0.4,1,1S3.6,13,3,13z"></path><path d="M23,13h-2c-0.6,0-1-0.4-1-1s0.4-1,1-1h2c0.6,0,1,0.4,1,1S23.6,13,23,13z"></path><path d="M4.2,20.8c-0.3,0-0.5-0.1-0.7-0.3c-0.4-0.4-0.4-1,0-1.4l1.4-1.4c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-1.4,1.4C4.7,20.7,4.5,20.8,4.2,20.8z"></path><path d="M18.4,6.6c-0.3,0-0.5-0.1-0.7-0.3c-0.4-0.4-0.4-1,0-1.4l1.4-1.4c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-1.4,1.4C18.9,6.5,18.6,6.6,18.4,6.6z"></path></svg><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-switch-appearance-moon"><path d="M12.1,22c-0.3,0-0.6,0-0.9,0c-5.5-0.5-9.5-5.4-9-10.9c0.4-4.8,4.2-8.6,9-9c0.4,0,0.8,0.2,1,0.5c0.2,0.3,0.2,0.8-0.1,1.1c-2,2.7-1.4,6.4,1.3,8.4c2.1,1.6,5,1.6,7.1,0c0.3-0.2,0.7-0.3,1.1-0.1c0.3,0.2,0.5,0.6,0.5,1c-0.2,2.7-1.5,5.1-3.6,6.8C16.6,21.2,14.4,22,12.1,22zM9.3,4.4c-2.9,1-5,3.6-5.2,6.8c-0.4,4.4,2.8,8.3,7.2,8.7c2.1,0.2,4.2-0.4,5.8-1.8c1.1-0.9,1.9-2.1,2.4-3.4c-2.5,0.9-5.3,0.5-7.5-1.1C9.2,11.4,8.1,7.7,9.3,4.4z"></path></svg><!--]--></span></span></button></div><div class="vt-social-links VPNavBarSocialLinks social-links" data-v-96a69ce8 data-v-8b9a7f88><!--[--><a class="vt-social-link is-small" href="/translations/" title="languages" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M0 0h24v24H0z" fill="none"></path><path d=" M12.87 15.07l-2.54-2.51.03-.03c1.74-1.94 2.98-4.17 3.71-6.53H17V4h-7V2H8v2H1v1.99h11.17C11.5 7.92 10.44 9.75 9 11.35 8.07 10.32 7.3 9.19 6.69 8h-2c.73 1.63 1.73 3.17 2.98 4.56l-5.09 5.02L4 19l5-5 3.11 3.11.76-2.04zM18.5 10h-2L12 22h2l1.12-3h4.75L21 22h2l-4.5-12zm-2.62 7l1.62-4.33L19.12 17h-3.24z " class="css-c4d79v"></path></svg><span class="visually-hidden">languages</span></a><a class="vt-social-link is-small" href="https://work.weixin.qq.com/kfid/kfc8e47e8eca8390e16" title="github" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M12 .297c-6.63 0-12 5.373-12 12 0 5.303 3.438 9.8 8.205 11.385.6.113.82-.258.82-.577 0-.285-.01-1.04-.015-2.04-3.338.724-4.042-1.61-4.042-1.61C4.422 18.07 3.633 17.7 3.633 17.7c-1.087-.744.084-.729.084-.729 1.205.084 1.838 1.236 1.838 1.236 1.07 1.835 2.809 1.305 3.495.998.108-.776.417-1.305.76-1.605-2.665-.3-5.466-1.332-5.466-5.93 0-1.31.465-2.38 1.235-3.22-.135-.303-.54-1.523.105-3.176 0 0 1.005-.322 3.3 1.23.96-.267 1.98-.399 3-.405 1.02.006 2.04.138 3 .405 2.28-1.552 3.285-1.23 3.285-1.23.645 1.653.24 2.873.12 3.176.765.84 1.23 1.91 1.23 3.22 0 4.61-2.805 5.625-5.475 5.92.42.36.81 1.096.81 2.22 0 1.606-.015 2.896-.015 3.286 0 .315.21.69.825.57C20.565 22.092 24 17.592 24 12.297c0-6.627-5.373-12-12-12"></path></svg><span class="visually-hidden">github</span></a><a class="vt-social-link is-small" href="mailto:dibinkf@vip.qq.com" title="slack" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M5.042 15.165a2.528 2.528 0 0 1-2.52 2.523A2.528 2.528 0 0 1 0 15.165a2.527 2.527 0 0 1 2.522-2.52h2.52v2.52zM6.313 15.165a2.527 2.527 0 0 1 2.521-2.52 2.527 2.527 0 0 1 2.521 2.52v6.313A2.528 2.528 0 0 1 8.834 24a2.528 2.528 0 0 1-2.521-2.522v-6.313zM8.834 5.042a2.528 2.528 0 0 1-2.521-2.52A2.528 2.528 0 0 1 8.834 0a2.528 2.528 0 0 1 2.521 2.522v2.52H8.834zM8.834 6.313a2.528 2.528 0 0 1 2.521 2.521 2.528 2.528 0 0 1-2.521 2.521H2.522A2.528 2.528 0 0 1 0 8.834a2.528 2.528 0 0 1 2.522-2.521h6.312zM18.956 8.834a2.528 2.528 0 0 1 2.522-2.521A2.528 2.528 0 0 1 24 8.834a2.528 2.528 0 0 1-2.522 2.521h-2.522V8.834zM17.688 8.834a2.528 2.528 0 0 1-2.523 2.521 2.527 2.527 0 0 1-2.52-2.521V2.522A2.527 2.527 0 0 1 15.165 0a2.528 2.528 0 0 1 2.523 2.522v6.312zM15.165 18.956a2.528 2.528 0 0 1 2.523 2.522A2.528 2.528 0 0 1 15.165 24a2.527 2.527 0 0 1-2.52-2.522v-2.522h2.52zM15.165 17.688a2.527 2.527 0 0 1-2.52-2.523 2.526 2.526 0 0 1 2.52-2.52h6.313A2.527 2.527 0 0 1 24 15.165a2.528 2.528 0 0 1-2.522 2.523h-6.313z"></path></svg><span class="visually-hidden">slack</span></a><a class="vt-social-link is-small" href="tencent://message/?uin=1974892005" title="discord" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M20.222 0c1.406 0 2.54 1.137 2.607 2.475V24l-2.677-2.273-1.47-1.338-1.604-1.398.67 2.205H3.71c-1.402 0-2.54-1.065-2.54-2.476V2.48C1.17 1.142 2.31.003 3.715.003h16.5L20.222 0zm-6.118 5.683h-.03l-.202.2c2.073.6 3.076 1.537 3.076 1.537-1.336-.668-2.54-1.002-3.744-1.137-.87-.135-1.74-.064-2.475 0h-.2c-.47 0-1.47.2-2.81.735-.467.203-.735.336-.735.336s1.002-1.002 3.21-1.537l-.135-.135s-1.672-.064-3.477 1.27c0 0-1.805 3.144-1.805 7.02 0 0 1 1.74 3.743 1.806 0 0 .4-.533.805-1.002-1.54-.468-2.14-1.404-2.14-1.404s.134.066.335.2h.06c.03 0 .044.015.06.03v.006c.016.016.03.03.06.03.33.136.66.27.93.4.466.202 1.065.403 1.8.536.93.135 1.996.2 3.21 0 .6-.135 1.2-.267 1.8-.535.39-.2.87-.4 1.397-.737 0 0-.6.936-2.205 1.404.33.466.795 1 .795 1 2.744-.06 3.81-1.8 3.87-1.726 0-3.87-1.815-7.02-1.815-7.02-1.635-1.214-3.165-1.26-3.435-1.26l.056-.02zm.168 4.413c.703 0 1.27.6 1.27 1.335 0 .74-.57 1.34-1.27 1.34-.7 0-1.27-.6-1.27-1.334.002-.74.573-1.338 1.27-1.338zm-4.543 0c.7 0 1.266.6 1.266 1.335 0 .74-.57 1.34-1.27 1.34-.7 0-1.27-.6-1.27-1.334 0-.74.57-1.338 1.27-1.338z"></path></svg><span class="visually-hidden">discord</span></a><!--]--></div><div class="vt-flyout VPNavBarExtra extra" data-v-96a69ce8 data-v-b3e218c4><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false" aria-label="extra navigation"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-icon"><circle cx="12" cy="12" r="2"></circle><circle cx="19" cy="12" r="2"></circle><circle cx="5" cy="12" r="2"></circle></svg></button><div class="vt-flyout-menu"><div class="vt-menu"><!----><!--[--><!--[--><div class="vt-menu-group" data-v-b3e218c4><div class="vt-menu-item item" data-v-b3e218c4><p class="vt-menu-label" data-v-b3e218c4>Appearance</p><div class="vt-menu-action action" data-v-b3e218c4><button class="vt-switch vt-switch-appearance" type="button" role="switch" aria-label="toggle dark mode" data-v-b3e218c4><span class="vt-switch-check"><span class="vt-switch-icon"><!--[--><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-switch-appearance-sun"><path d="M12,18c-3.3,0-6-2.7-6-6s2.7-6,6-6s6,2.7,6,6S15.3,18,12,18zM12,8c-2.2,0-4,1.8-4,4c0,2.2,1.8,4,4,4c2.2,0,4-1.8,4-4C16,9.8,14.2,8,12,8z"></path><path d="M12,4c-0.6,0-1-0.4-1-1V1c0-0.6,0.4-1,1-1s1,0.4,1,1v2C13,3.6,12.6,4,12,4z"></path><path d="M12,24c-0.6,0-1-0.4-1-1v-2c0-0.6,0.4-1,1-1s1,0.4,1,1v2C13,23.6,12.6,24,12,24z"></path><path d="M5.6,6.6c-0.3,0-0.5-0.1-0.7-0.3L3.5,4.9c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l1.4,1.4c0.4,0.4,0.4,1,0,1.4C6.2,6.5,5.9,6.6,5.6,6.6z"></path><path d="M19.8,20.8c-0.3,0-0.5-0.1-0.7-0.3l-1.4-1.4c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l1.4,1.4c0.4,0.4,0.4,1,0,1.4C20.3,20.7,20,20.8,19.8,20.8z"></path><path d="M3,13H1c-0.6,0-1-0.4-1-1s0.4-1,1-1h2c0.6,0,1,0.4,1,1S3.6,13,3,13z"></path><path d="M23,13h-2c-0.6,0-1-0.4-1-1s0.4-1,1-1h2c0.6,0,1,0.4,1,1S23.6,13,23,13z"></path><path d="M4.2,20.8c-0.3,0-0.5-0.1-0.7-0.3c-0.4-0.4-0.4-1,0-1.4l1.4-1.4c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-1.4,1.4C4.7,20.7,4.5,20.8,4.2,20.8z"></path><path d="M18.4,6.6c-0.3,0-0.5-0.1-0.7-0.3c-0.4-0.4-0.4-1,0-1.4l1.4-1.4c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-1.4,1.4C18.9,6.5,18.6,6.6,18.4,6.6z"></path></svg><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-switch-appearance-moon"><path d="M12.1,22c-0.3,0-0.6,0-0.9,0c-5.5-0.5-9.5-5.4-9-10.9c0.4-4.8,4.2-8.6,9-9c0.4,0,0.8,0.2,1,0.5c0.2,0.3,0.2,0.8-0.1,1.1c-2,2.7-1.4,6.4,1.3,8.4c2.1,1.6,5,1.6,7.1,0c0.3-0.2,0.7-0.3,1.1-0.1c0.3,0.2,0.5,0.6,0.5,1c-0.2,2.7-1.5,5.1-3.6,6.8C16.6,21.2,14.4,22,12.1,22zM9.3,4.4c-2.9,1-5,3.6-5.2,6.8c-0.4,4.4,2.8,8.3,7.2,8.7c2.1,0.2,4.2-0.4,5.8-1.8c1.1-0.9,1.9-2.1,2.4-3.4c-2.5,0.9-5.3,0.5-7.5-1.1C9.2,11.4,8.1,7.7,9.3,4.4z"></path></svg><!--]--></span></span></button></div></div></div><div class="vt-menu-group" data-v-b3e218c4><div class="vt-menu-item item" data-v-b3e218c4><div class="vt-social-links social-links" data-v-b3e218c4><!--[--><a class="vt-social-link is-small" href="/translations/" title="languages" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M0 0h24v24H0z" fill="none"></path><path d=" M12.87 15.07l-2.54-2.51.03-.03c1.74-1.94 2.98-4.17 3.71-6.53H17V4h-7V2H8v2H1v1.99h11.17C11.5 7.92 10.44 9.75 9 11.35 8.07 10.32 7.3 9.19 6.69 8h-2c.73 1.63 1.73 3.17 2.98 4.56l-5.09 5.02L4 19l5-5 3.11 3.11.76-2.04zM18.5 10h-2L12 22h2l1.12-3h4.75L21 22h2l-4.5-12zm-2.62 7l1.62-4.33L19.12 17h-3.24z " class="css-c4d79v"></path></svg><span class="visually-hidden">languages</span></a><a class="vt-social-link is-small" href="https://work.weixin.qq.com/kfid/kfc8e47e8eca8390e16" title="github" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M12 .297c-6.63 0-12 5.373-12 12 0 5.303 3.438 9.8 8.205 11.385.6.113.82-.258.82-.577 0-.285-.01-1.04-.015-2.04-3.338.724-4.042-1.61-4.042-1.61C4.422 18.07 3.633 17.7 3.633 17.7c-1.087-.744.084-.729.084-.729 1.205.084 1.838 1.236 1.838 1.236 1.07 1.835 2.809 1.305 3.495.998.108-.776.417-1.305.76-1.605-2.665-.3-5.466-1.332-5.466-5.93 0-1.31.465-2.38 1.235-3.22-.135-.303-.54-1.523.105-3.176 0 0 1.005-.322 3.3 1.23.96-.267 1.98-.399 3-.405 1.02.006 2.04.138 3 .405 2.28-1.552 3.285-1.23 3.285-1.23.645 1.653.24 2.873.12 3.176.765.84 1.23 1.91 1.23 3.22 0 4.61-2.805 5.625-5.475 5.92.42.36.81 1.096.81 2.22 0 1.606-.015 2.896-.015 3.286 0 .315.21.69.825.57C20.565 22.092 24 17.592 24 12.297c0-6.627-5.373-12-12-12"></path></svg><span class="visually-hidden">github</span></a><a class="vt-social-link is-small" href="mailto:dibinkf@vip.qq.com" title="slack" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M5.042 15.165a2.528 2.528 0 0 1-2.52 2.523A2.528 2.528 0 0 1 0 15.165a2.527 2.527 0 0 1 2.522-2.52h2.52v2.52zM6.313 15.165a2.527 2.527 0 0 1 2.521-2.52 2.527 2.527 0 0 1 2.521 2.52v6.313A2.528 2.528 0 0 1 8.834 24a2.528 2.528 0 0 1-2.521-2.522v-6.313zM8.834 5.042a2.528 2.528 0 0 1-2.521-2.52A2.528 2.528 0 0 1 8.834 0a2.528 2.528 0 0 1 2.521 2.522v2.52H8.834zM8.834 6.313a2.528 2.528 0 0 1 2.521 2.521 2.528 2.528 0 0 1-2.521 2.521H2.522A2.528 2.528 0 0 1 0 8.834a2.528 2.528 0 0 1 2.522-2.521h6.312zM18.956 8.834a2.528 2.528 0 0 1 2.522-2.521A2.528 2.528 0 0 1 24 8.834a2.528 2.528 0 0 1-2.522 2.521h-2.522V8.834zM17.688 8.834a2.528 2.528 0 0 1-2.523 2.521 2.527 2.527 0 0 1-2.52-2.521V2.522A2.527 2.527 0 0 1 15.165 0a2.528 2.528 0 0 1 2.523 2.522v6.312zM15.165 18.956a2.528 2.528 0 0 1 2.523 2.522A2.528 2.528 0 0 1 15.165 24a2.527 2.527 0 0 1-2.52-2.522v-2.522h2.52zM15.165 17.688a2.527 2.527 0 0 1-2.52-2.523 2.526 2.526 0 0 1 2.52-2.52h6.313A2.527 2.527 0 0 1 24 15.165a2.528 2.528 0 0 1-2.522 2.523h-6.313z"></path></svg><span class="visually-hidden">slack</span></a><a class="vt-social-link is-small" href="tencent://message/?uin=1974892005" title="discord" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M20.222 0c1.406 0 2.54 1.137 2.607 2.475V24l-2.677-2.273-1.47-1.338-1.604-1.398.67 2.205H3.71c-1.402 0-2.54-1.065-2.54-2.476V2.48C1.17 1.142 2.31.003 3.715.003h16.5L20.222 0zm-6.118 5.683h-.03l-.202.2c2.073.6 3.076 1.537 3.076 1.537-1.336-.668-2.54-1.002-3.744-1.137-.87-.135-1.74-.064-2.475 0h-.2c-.47 0-1.47.2-2.81.735-.467.203-.735.336-.735.336s1.002-1.002 3.21-1.537l-.135-.135s-1.672-.064-3.477 1.27c0 0-1.805 3.144-1.805 7.02 0 0 1 1.74 3.743 1.806 0 0 .4-.533.805-1.002-1.54-.468-2.14-1.404-2.14-1.404s.134.066.335.2h.06c.03 0 .044.015.06.03v.006c.016.016.03.03.06.03.33.136.66.27.93.4.466.202 1.065.403 1.8.536.93.135 1.996.2 3.21 0 .6-.135 1.2-.267 1.8-.535.39-.2.87-.4 1.397-.737 0 0-.6.936-2.205 1.404.33.466.795 1 .795 1 2.744-.06 3.81-1.8 3.87-1.726 0-3.87-1.815-7.02-1.815-7.02-1.635-1.214-3.165-1.26-3.435-1.26l.056-.02zm.168 4.413c.703 0 1.27.6 1.27 1.335 0 .74-.57 1.34-1.27 1.34-.7 0-1.27-.6-1.27-1.334.002-.74.573-1.338 1.27-1.338zm-4.543 0c.7 0 1.266.6 1.266 1.335 0 .74-.57 1.34-1.27 1.34-.7 0-1.27-.6-1.27-1.334 0-.74.57-1.338 1.27-1.338z"></path></svg><span class="visually-hidden">discord</span></a><!--]--></div></div></div><!--]--><!--]--></div></div></div><button type="button" class="vt-hamburger VPNavBarHamburger hamburger" aria-label="mobile navigation" aria-expanded="false" aria-controls="VPNavScreen" data-v-96a69ce8 data-v-c78f86de><span class="vt-hamburger-container"><span class="vt-hamburger-top"></span><span class="vt-hamburger-middle"></span><span class="vt-hamburger-bottom"></span></span></button></div></div></div><!----></header><div class="VPLocalNav" data-v-23306c50 data-v-7cd5ed0a><button class="menu" aria-expanded="false" aria-controls="VPSidebarNav" data-v-7cd5ed0a><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="menu-icon" data-v-7cd5ed0a><path d="M17,11H3c-0.6,0-1-0.4-1-1s0.4-1,1-1h14c0.6,0,1,0.4,1,1S17.6,11,17,11z"></path><path d="M21,7H3C2.4,7,2,6.6,2,6s0.4-1,1-1h18c0.6,0,1,0.4,1,1S21.6,7,21,7z"></path><path d="M21,15H3c-0.6,0-1-0.4-1-1s0.4-1,1-1h18c0.6,0,1,0.4,1,1S21.6,15,21,15z"></path><path d="M17,19H3c-0.6,0-1-0.4-1-1s0.4-1,1-1h14c0.6,0,1,0.4,1,1S17.6,19,17,19z"></path></svg><span class="menu-text" data-v-7cd5ed0a>Menu</span></button><a class="top-link" href="#" data-v-7cd5ed0a>Return to top</a></div><aside class="VPSidebar" data-v-23306c50 data-v-22052bdb><nav id="VPSidebarNav" aria-labelledby="sidebar-aria-label" tabindex="-1" data-v-22052bdb><!--[--><!--]--><span id="sidebar-aria-label" class="visually-hidden" data-v-22052bdb>Sidebar Navigation</span><!--[--><div class="group" data-v-22052bdb><section class="VPSidebarGroup" data-v-22052bdb data-v-59eaa146><div class="title" data-v-59eaa146><h2 class="active title-text" data-v-59eaa146>mysql</h2></div><!--[--><a class="link" href="/mysql/01.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>01 【数据库概述】</p></a><a class="link" href="/mysql/02.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>02 【MySQL基本使用】</p></a><a class="link" href="/mysql/03.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>03 【基本的SELECT语句】</p></a><a class="link" href="/mysql/04.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>04 【运算符】</p></a><a class="link" href="/mysql/05.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>05 【排序与分页】</p></a><a class="link active" href="/mysql/06.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>06 【多表查询】</p></a><a class="link" href="/mysql/07.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>07 【单行函数】</p></a><a class="link" href="/mysql/08.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>08 【聚合函数与分组查询】</p></a><a class="link" href="/mysql/09.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>09 【子查询】</p></a><a class="link" href="/mysql/10.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>10 【创建和管理表】</p></a><a class="link" href="/mysql/11.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>11 【数据处理之增删改】</p></a><!--]--></section></div><!--]--><!--[--><!--]--></nav></aside><div id="VPContent" class="VPContent has-sidebar" data-v-23306c50 data-v-790151bc><div class="VPContentDoc has-aside has-sidebar" data-v-790151bc data-v-4fe9b7bd><div class="container" data-v-4fe9b7bd><div class="aside" data-v-4fe9b7bd><div class="aside-container" data-v-4fe9b7bd><!--[--><!--]--><div class="VPContentDocOutline" data-v-4fe9b7bd data-v-aa0e2252><div class="outline-marker" data-v-aa0e2252></div><div class="outline-title" data-v-aa0e2252>On this page</div><nav aria-labelledby="doc-outline-aria-label" data-v-aa0e2252><span id="doc-outline-aria-label" class="visually-hidden" data-v-aa0e2252>Table of Contents for current page</span><ul class="root" data-v-aa0e2252><!--[--><li style="" data-v-aa0e2252><a class="outline-link" href="#_1-一个案例引发的多表连接" data-v-aa0e2252>1.一个案例引发的多表连接</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_2-多表查询分类讲解" data-v-aa0e2252>2. 多表查询分类讲解</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_3-sql99语法实现多表查询" data-v-aa0e2252>3.SQL99语法实现多表查询</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_4-union的使用" data-v-aa0e2252>4.UNION的使用</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_5-7种sql-joins的实现" data-v-aa0e2252>5.7种SQL JOINS的实现</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_6-sql99语法新特性" data-v-aa0e2252>6.SQL99语法新特性</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_7-章节小结" data-v-aa0e2252>7.章节小结</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_8-练习" data-v-aa0e2252>8.练习</a><!----></li><!--]--></ul></nav></div><!--[--><!--[--><!--[--><div><a class="sponsors-aside-text" href="/">联系客服</a><img src="/images/qrcode.png"></div><!--]--><!--]--><!--]--><div class="VPCarbonAds" data-v-4fe9b7bd><img src="/logo.svg" alt="" srcset=""></div><!--[--><!--[--><!--[--><h1 class="tagline" data-v-3916b2f0><span class="accent" data-v-3916b2f0>Bin</span><br data-v-3916b2f0>QQ - 1974892005 </h1><!--]--><!--]--><!--]--></div></div><div class="content" data-v-4fe9b7bd><!--[--><!--]--><main data-v-4fe9b7bd><div style="position:relative;" class="vt-doc mysql" data-v-4fe9b7bd><div><h1 id="_06-【多表查询】" tabindex="-1">06 【多表查询】 <a class="header-anchor" href="#_06-【多表查询】" aria-hidden="true">#</a></h1><p>多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。</p><p>前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。</p><h2 id="_1-一个案例引发的多表连接" tabindex="-1">1.一个案例引发的多表连接 <a class="header-anchor" href="#_1-一个案例引发的多表连接" aria-hidden="true">#</a></h2><h3 id="_1-1-案例说明" tabindex="-1">1.1 案例说明 <a class="header-anchor" href="#_1-1-案例说明" aria-hidden="true">#</a></h3><p><img src="https://i0.hdslb.com/bfs/album/dbd56a91d1ed9a3ef59ecfc9082d72debefdf6b9.png" alt=""></p><p>从多个表中获取数据：</p><p><img src="https://i0.hdslb.com/bfs/album/b6259a28a729726b4efc17e228c4fab663310316.png" alt=""></p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">#案例：查询员工的姓名及其部门名称</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name, department_name</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees, departments;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><img src="https://i0.hdslb.com/bfs/album/788db294c05c5616353a782d45db49c4fb194f4d.png" alt=""></p><p>查询结果：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-----------+----------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| last_name | department_name      |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-----------+----------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| King      | Administration       |</span></span>
<span class="line"><span style="color:#A6ACCD;">| King      | Marketing            |</span></span>
<span class="line"><span style="color:#A6ACCD;">| King      | Purchasing           |</span></span>
<span class="line"><span style="color:#A6ACCD;">| King      | Human Resources      |</span></span>
<span class="line"><span style="color:#A6ACCD;">| King      | Shipping             |</span></span>
<span class="line"><span style="color:#A6ACCD;">| King      | IT                   |</span></span>
<span class="line"><span style="color:#A6ACCD;">| King      | Public Relations     |</span></span>
<span class="line"><span style="color:#A6ACCD;">| King      | Sales                |</span></span>
<span class="line"><span style="color:#A6ACCD;">| King      | Executive            |</span></span>
<span class="line"><span style="color:#A6ACCD;">| King      | Finance              |</span></span>
<span class="line"><span style="color:#A6ACCD;">| King      | Accounting           |</span></span>
<span class="line"><span style="color:#A6ACCD;">| King      | Treasury             |</span></span>
<span class="line"><span style="color:#A6ACCD;">...</span></span>
<span class="line"><span style="color:#A6ACCD;">| Gietz     | IT Support           |</span></span>
<span class="line"><span style="color:#A6ACCD;">| Gietz     | NOC                  |</span></span>
<span class="line"><span style="color:#A6ACCD;">| Gietz     | IT Helpdesk          |</span></span>
<span class="line"><span style="color:#A6ACCD;">| Gietz     | Government Sales     |</span></span>
<span class="line"><span style="color:#A6ACCD;">| Gietz     | Retail Sales         |</span></span>
<span class="line"><span style="color:#A6ACCD;">| Gietz     | Recruiting           |</span></span>
<span class="line"><span style="color:#A6ACCD;">| Gietz     | Payroll              |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-----------+----------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">2889</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">rows</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">01</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br></div></div><p><strong>分析错误情况：</strong></p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">COUNT</span><span style="color:#A6ACCD;">(employee_id) </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees;</span></span>
<span class="line"><span style="color:#A6ACCD;">#输出107行</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">COUNT</span><span style="color:#A6ACCD;">(department_id)</span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> departments;</span></span>
<span class="line"><span style="color:#A6ACCD;">#输出27行</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">107</span><span style="color:#89DDFF;">*</span><span style="color:#F78C6C;">27</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> dual;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><p>我们把上述多表查询中出现的问题称为：笛卡尔积的错误。</p><h3 id="_1-2-笛卡尔积（或交叉连接）的理解" tabindex="-1">1.2 笛卡尔积（或交叉连接）的理解 <a class="header-anchor" href="#_1-2-笛卡尔积（或交叉连接）的理解" aria-hidden="true">#</a></h3><p>笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的<code>所有可能组合</code>，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。</p><blockquote><p><strong>表一和表二做笛卡尔积，就是将表一的每一行和表二的每一行进行拼接</strong></p></blockquote><p><img src="https://i0.hdslb.com/bfs/album/4b0ab3f5bb9797e459e56bc553a65b866b34b9ef.png" alt=""></p><p>SQL92中，笛卡尔积也称为<code>交叉连接</code>，英文是 <code>CROSS JOIN</code>。在 SQL99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。在MySQL中如下情况会出现笛卡尔积：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">#查询员工姓名和所在部门名称</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name,department_name </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees,departments;</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name,department_name </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees </span><span style="color:#F78C6C;">CROSS</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> departments;</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name,department_name </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees </span><span style="color:#F78C6C;">INNER JOIN</span><span style="color:#A6ACCD;"> departments;</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name,department_name </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees </span><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> departments;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><h3 id="_1-3-案例分析与问题解决" tabindex="-1">1.3 案例分析与问题解决 <a class="header-anchor" href="#_1-3-案例分析与问题解决" aria-hidden="true">#</a></h3><ul><li><p><strong>笛卡尔积的错误会在下面条件下产生</strong>：</p><ul><li>省略多个表的连接条件（或关联条件）</li><li>连接条件（或关联条件）无效</li><li>所有表中的所有行互相连接</li></ul></li><li><p><strong><code>为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。</code></strong></p></li><li><p>加入连接条件后，查询语法：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;">	table1.column, table2.column</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;">	table1, table2</span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;">	table1.column1 </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> table2.column2;  #连接条件</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><ul><li><strong>在 WHERE子句中写入连接条件。</strong></li></ul></li><li><p>正确写法：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">#案例：查询员工的姓名及其部门名称</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name, department_name</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees, departments</span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> employees.department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> departments.department_id;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div></li><li><p><strong>在表中有相同列时，在列名之前加上表名前缀。</strong></p></li></ul><h2 id="_2-多表查询分类讲解" tabindex="-1">2. 多表查询分类讲解 <a class="header-anchor" href="#_2-多表查询分类讲解" aria-hidden="true">#</a></h2><h3 id="_2-1-分类1：等值连接-vs-非等值连接" tabindex="-1">2.1 分类1：等值连接 vs 非等值连接 <a class="header-anchor" href="#_2-1-分类1：等值连接-vs-非等值连接" aria-hidden="true">#</a></h3><h4 id="_2-1-1-等值连接" tabindex="-1">2.1.1 等值连接 <a class="header-anchor" href="#_2-1-1-等值连接" aria-hidden="true">#</a></h4><p><img src="https://i0.hdslb.com/bfs/album/ebcdbd2da70c3986c423ead9afcc69d10784b494.png" alt=""></p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employees.employee_id, employees.last_name, </span></span>
<span class="line"><span style="color:#A6ACCD;">       employees.department_id, departments.department_id,</span></span>
<span class="line"><span style="color:#A6ACCD;">       departments.location_id</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;">   employees, departments</span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;">  employees.department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> departments.department_id;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p><img src="https://i0.hdslb.com/bfs/album/450ad7fb6831a42f6fb311b8d532d5c7c6693451.png" alt=""></p><p><strong>拓展1：多个连接条件与 AND 操作符</strong></p><p><img src="https://i0.hdslb.com/bfs/album/1b51f07cb253c6391c5099de2826153e9ebadebd.png" alt=""></p><p><strong>拓展2：区分重复的列名</strong></p><ul><li><strong><code>多个表中有相同列时，必须在列名之前加上表名前缀。</code></strong></li><li>在不同表中具有相同列名的列可以用<code>表名</code>加以区分。</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employees.last_name, departments.department_name,employees.department_id</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees, departments</span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> employees.department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> departments.department_id;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><strong>拓展3：表的别名</strong></p><ul><li>使用别名可以简化查询。</li><li>列名前使用表名前缀可以提高查询效率。</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> e.employee_id, e.last_name, e.department_id,</span></span>
<span class="line"><span style="color:#A6ACCD;">       d.department_id, d.location_id</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;">   employees e , departments d</span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;">  e.department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.department_id;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><blockquote><p>需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。</p></blockquote><blockquote><p><code>阿里开发规范</code>：</p><p>【<code>强制</code>】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或 表名）进行限定。</p><p><code>说明</code>：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。</p><p><code>正例</code>：select <a href="http://t1.name" target="_blank" rel="noopener noreferrer">t1.name</a> from table_first as t1 , table_second as t2 where <a href="http://t1.id=t2.id" target="_blank" rel="noopener noreferrer">t1.id=t2.id</a>;</p><p><code>反例</code>：在某业务中，由于多表关联查询语句没有加表的别名（或表名）的限制，正常运行两年后，最近在 某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现出 1052 异常：Column &#39;name&#39; in field list is ambiguous。</p></blockquote><p><strong>拓展4：连接多个表</strong></p><p><img src="https://i0.hdslb.com/bfs/album/a1e66c17d094211d8628b70b4975ad7265a0e6e6.png" alt=""></p><p>**总结：连接 n个表,至少需要n-1个连接条件。**比如，连接三个表，至少需要两个连接条件。</p><p>练习：查询出公司员工的 last_name,department_name, city</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> empl.last_name,dept.department_name,loca.city</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees </span><span style="color:#F78C6C;">AS</span><span style="color:#A6ACCD;"> empl </span></span>
<span class="line"><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> departments </span><span style="color:#F78C6C;">AS</span><span style="color:#A6ACCD;"> dept </span><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> empl.department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> dept.department_id </span></span>
<span class="line"><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> locations </span><span style="color:#F78C6C;">AS</span><span style="color:#A6ACCD;"> loca </span><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> loca.location_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> dept.location_id</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h4 id="_2-1-2-非等值连接" tabindex="-1">2.1.2 非等值连接 <a class="header-anchor" href="#_2-1-2-非等值连接" aria-hidden="true">#</a></h4><p><img src="https://i0.hdslb.com/bfs/album/4683d48086df2578216966cb6d630165793e3722.png" alt=""></p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> e.last_name, e.salary, j.grade_level</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;">   employees e, job_grades j</span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;">  e.salary </span><span style="color:#F78C6C;">BETWEEN</span><span style="color:#A6ACCD;"> j.lowest_sal </span><span style="color:#F78C6C;">AND</span><span style="color:#A6ACCD;"> j.highest_sal;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><img src="https://i0.hdslb.com/bfs/album/6660a016d838fcb8ebc8ff3b2f9f5eb7b6909579.png" alt=""></p><h3 id="_2-2-分类2：自连接-vs-非自连接" tabindex="-1">2.2 分类2：自连接 vs 非自连接 <a class="header-anchor" href="#_2-2-分类2：自连接-vs-非自连接" aria-hidden="true">#</a></h3><p><img src="https://i0.hdslb.com/bfs/album/8516b9f9f01a034af9035d4bdfd494bfecd923f1.png" alt=""></p><p>当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。</p><p><strong>题目：查询employees表，返回“Xxx works for Xxx”</strong></p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">CONCAT</span><span style="color:#A6ACCD;">(worker.last_name ,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;"> works for </span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">       , manager.last_name)</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;">   employees worker, employees manager</span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;">  worker.manager_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> manager.employee_id ;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><img src="https://i0.hdslb.com/bfs/album/c0930ff29b358ba0009832df134172a736a40172.png" alt=""></p><p>练习：查询出last_name为 ‘Chen’ 的员工的 manager 的信息。</p><h3 id="_2-3-分类3：内连接-vs-外连接" tabindex="-1">2.3 分类3：内连接 vs 外连接 <a class="header-anchor" href="#_2-3-分类3：内连接-vs-外连接" aria-hidden="true">#</a></h3><p>==内连接包括：等值连接、非等值连接、自连接==</p><p>==外连接包括：左外连接、右外连接、满外连接==</p><blockquote><p><strong>所有的这些连接都是先将这两张表做笛卡尔积，然后留下其中某些列，删除其他列</strong></p></blockquote><p>除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。</p><p><img src="https://i0.hdslb.com/bfs/album/b51c923a4b66f1412dce297326ed2533078f43e4.png" alt=""></p><ul><li><p>内连接: 合并具有同一列的两个以上的表的行, <strong>结果集中不包含一个表与另一个表不匹配的行</strong><img src="https://i0.hdslb.com/bfs/album/209e3180db54eca5fb867da49cf84b65df9842d8.png" alt=""></p></li><li><p>外连接: 两个表在连接过程中除了返回满足连接条件的行以外<strong>还返回左（或右）表中不满足条件的行</strong> <strong>，这种连接称为左（或右） 外连接</strong>。没有匹配的行时, 结果表中相应的列为空(NULL)。</p></li><li><p>如果是左外连接，则连接条件中左边的表也称为<code>主表</code>，右边的表称为<code>从表</code>。</p><p>如果是右外连接，则连接条件中右边的表也称为<code>主表</code>，左边的表称为<code>从表</code>。</p></li></ul><blockquote><p><strong>Outer是可以省略的</strong></p><p>Left Join=Left Outer Join 即左连接就是左外连接</p><p>Right Join=Right Outer Join 即右连接就是右外连接</p></blockquote><p>举个例子：下面两张表</p><p><img src="https://i0.hdslb.com/bfs/album/1493aa8c8d85be9b144426b6adfffe444df353f3.png" alt=""></p><p><img src="https://i0.hdslb.com/bfs/album/6a0353394f02d8010c41ae0adf1f85674fbcabb5.png" alt=""></p><p>Persons表和Orders表进行笛卡尔积得到：</p><p><img src="https://i0.hdslb.com/bfs/album/7883bd24e7b17296730a2f30a6569a541da33a6b.png" alt=""></p><p>运行Mysql语句：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> Persons.LastName, Persons.FirstName, Orders.OrderNo</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> Persons</span></span>
<span class="line"><span style="color:#F78C6C;">LEFT JOIN</span><span style="color:#A6ACCD;"> Orders</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> Persons.Id_P</span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;">Orders.Id_P</span></span>
<span class="line"><span style="color:#F78C6C;">ORDER BY</span><span style="color:#A6ACCD;"> Persons.LastName</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>就是在上面的笛卡尔积中，只保存Persons.Id_P=Orders.Id_P的行（也就是红色四行）</p><p><img src="https://i0.hdslb.com/bfs/album/632853a19cf85fd1686b7e962cf1d38dd45e29d9.png" alt=""></p><p>最后由于是Persons Left Join Orders，所以Persons表是主表，因此Persons表是主表，主表Person表中有些行在Orders表中匹配不到，但是也要添加到最后的结果里面，最后结果为：</p><p><img src="https://i0.hdslb.com/bfs/album/873246af6ef22213d1ebf075278fc8961d924fb1.png" alt=""></p><p>如果是右连接：（<strong>也就是主表不再是Persons表了，而是Orders表</strong>）</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> Persons.LastName, Persons.FirstName, Orders.OrderNo</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> Persons</span></span>
<span class="line"><span style="color:#F78C6C;">RIGHT JOIN</span><span style="color:#A6ACCD;"> Orders</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> Persons.Id_P</span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;">Orders.Id_P</span></span>
<span class="line"><span style="color:#F78C6C;">ORDER BY</span><span style="color:#A6ACCD;"> Persons.LastName</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>此时要保存的就是主表Orders中没有匹配到的行了（而不是Persons表中没有匹配到的行）</p><p><img src="https://i0.hdslb.com/bfs/album/0916eff9a89b5b3ac3659708049df7d9e6fd4f4a.png" alt=""></p><h3 id="_2-4-补充-on-和-where区别" tabindex="-1">2.4 补充 on 和 where区别 <a class="header-anchor" href="#_2-4-补充-on-和-where区别" aria-hidden="true">#</a></h3><p>两个表在，join时，首先做一个笛卡尔积，on后面的条件是对这个笛卡尔积做一个过滤形成一张临时表，如果没有where就直接返回结果，如果有where就对上一步的临时表再进行过滤。</p><p>在使用left jion时，on和where条件的区别如下：</p><p>1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。</p><p>2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉</p><p>如下：</p><p><img src="https://i0.hdslb.com/bfs/album/9abc4fefa4adad991f8b6696f3063c87a911da8d.png" alt=""></p><p><img src="https://i0.hdslb.com/bfs/album/137479c211e560c2d06947a37272ec28c2bd5dcc.png" alt=""></p><p><img src="https://i0.hdslb.com/bfs/album/da152db6a165dbe78b3cd48073ad0e1b7f3a574b.png" alt=""></p><h2 id="_3-sql99语法实现多表查询" tabindex="-1">3.SQL99语法实现多表查询 <a class="header-anchor" href="#_3-sql99语法实现多表查询" aria-hidden="true">#</a></h2><h3 id="_3-1-基本语法" tabindex="-1">3.1 基本语法 <a class="header-anchor" href="#_3-1-基本语法" aria-hidden="true">#</a></h3><ul><li><p>使用JOIN...ON子句创建连接的语法结构：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> table1.column, table2.column,table3.column</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> table1</span></span>
<span class="line"><span style="color:#A6ACCD;">    </span><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> table2 </span><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> table1 和 table2 的连接条件</span></span>
<span class="line"><span style="color:#A6ACCD;">        </span><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> table3 </span><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> table2 和 table3 的连接条件</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>它的嵌套逻辑类似我们使用的 FOR 循环：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">for t1 </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> table1:</span></span>
<span class="line"><span style="color:#A6ACCD;">    for t2 </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> table2:</span></span>
<span class="line"><span style="color:#A6ACCD;">       </span><span style="color:#F78C6C;">if</span><span style="color:#A6ACCD;"> condition1:</span></span>
<span class="line"><span style="color:#A6ACCD;">           for t3 </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> table3:</span></span>
<span class="line"><span style="color:#A6ACCD;">              </span><span style="color:#F78C6C;">if</span><span style="color:#A6ACCD;"> condition2:</span></span>
<span class="line"><span style="color:#A6ACCD;">                  </span><span style="color:#F78C6C;">output</span><span style="color:#A6ACCD;"> t1 </span><span style="color:#89DDFF;">+</span><span style="color:#A6ACCD;"> t2 </span><span style="color:#89DDFF;">+</span><span style="color:#A6ACCD;"> t3</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p>SQL99 采用的这种嵌套结构非常清爽、层次性更强、可读性更强，即使再多的表进行连接也都清晰可见。如果你采用 SQL92，可读性就会大打折扣。</p></li><li><p>语法说明：</p><ul><li><strong>可以使用 ON 子句指定额外的连接条件</strong>。</li><li>这个连接条件是与其它条件分开的。</li><li><strong>ON 子句使语句具有更高的易读性</strong>。</li><li>关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接</li></ul></li></ul><h3 id="_3-2-内连接-inner-join-的实现" tabindex="-1">3.2 内连接(INNER JOIN)的实现 <a class="header-anchor" href="#_3-2-内连接-inner-join-的实现" aria-hidden="true">#</a></h3><p>语法：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> 字段列表</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> A表 </span><span style="color:#F78C6C;">INNER JOIN</span><span style="color:#A6ACCD;"> B表</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> 关联条件</span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> 等其他子句;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>题目1：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> e.employee_id, e.last_name, e.department_id, </span></span>
<span class="line"><span style="color:#A6ACCD;">       d.department_id, d.location_id</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;">   employees e </span><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> departments d</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;">     (e.department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.department_id);</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><img src="https://i0.hdslb.com/bfs/album/42dcde5aed77d5b7f8831b84325715d48a4f9e29.png" alt=""></p><h3 id="_3-3-外连接-outer-join-的实现" tabindex="-1">3.3 外连接(OUTER JOIN)的实现 <a class="header-anchor" href="#_3-3-外连接-outer-join-的实现" aria-hidden="true">#</a></h3><h4 id="_3-3-1-左外连接-left-outer-join" tabindex="-1">3.3.1 左外连接(LEFT OUTER JOIN) <a class="header-anchor" href="#_3-3-1-左外连接-left-outer-join" aria-hidden="true">#</a></h4><ul><li>语法：</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">#实现查询结果是A</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> 字段列表</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> A表 </span><span style="color:#F78C6C;">LEFT JOIN</span><span style="color:#A6ACCD;"> B表</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> 关联条件</span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> 等其他子句;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><ul><li>举例：</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> e.last_name, e.department_id, d.department_name</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;">   employees e</span></span>
<span class="line"><span style="color:#F78C6C;">LEFT JOIN</span><span style="color:#A6ACCD;"> departments d</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;">   (e.department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.department_id) ;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><img src="https://i0.hdslb.com/bfs/album/c069e78ea05325c9a61786e812577390005ee4e7.png" alt=""></p><h4 id="_3-3-2-右外连接-right-outer-join" tabindex="-1">3.3.2 右外连接(RIGHT OUTER JOIN) <a class="header-anchor" href="#_3-3-2-右外连接-right-outer-join" aria-hidden="true">#</a></h4><ul><li>语法：</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">#实现查询结果是B</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> 字段列表</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> A表 </span><span style="color:#F78C6C;">RIGHT JOIN</span><span style="color:#A6ACCD;"> B表</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> 关联条件</span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> 等其他子句;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><ul><li>举例：</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> e.last_name, e.department_id, d.department_name</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;">   employees e</span></span>
<span class="line"><span style="color:#F78C6C;">RIGHT OUTER JOIN</span><span style="color:#A6ACCD;"> departments d</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;">    (e.department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.department_id) ;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><img src="https://i0.hdslb.com/bfs/album/46d22608cd937778fed6763b173404763e99279e.png" alt=""></p><h4 id="_3-3-3-满外连接-full-outer-join" tabindex="-1">3.3.3 满外连接(FULL OUTER JOIN) <a class="header-anchor" href="#_3-3-3-满外连接-full-outer-join" aria-hidden="true">#</a></h4><ul><li>满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。</li><li>SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。</li><li>需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN <strong>UNION</strong> RIGHT join代替。</li></ul><h2 id="_4-union的使用" tabindex="-1">4.UNION的使用 <a class="header-anchor" href="#_4-union的使用" aria-hidden="true">#</a></h2><p><strong>合并查询结果</strong> 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。</p><p>语法格式：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> column,... </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> table1</span></span>
<span class="line"><span style="color:#F78C6C;">UNION</span><span style="color:#A6ACCD;"> [ALL]</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> column,... </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> table2</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><strong>UNION操作符</strong></p><p><img src="https://i0.hdslb.com/bfs/album/c5734bcf59763d3f8eb8b9c5d901af47a581778b.png" alt=""></p><p>UNION 操作符返回两个查询的结果集的并集，去除重复记录。</p><p><strong>UNION ALL操作符</strong></p><p><img src="https://i0.hdslb.com/bfs/album/bee36fb86d89a8b363b736e0264012caa369ed59.png" alt=""></p><p>UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。</p><blockquote><p>注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。</p></blockquote><p>举例：查询部门编号&gt;90或邮箱包含a的员工信息</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">#方式1</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees </span><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> email </span><span style="color:#F78C6C;">LIKE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">%a%</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">OR</span><span style="color:#A6ACCD;"> department_id</span><span style="color:#89DDFF;">&gt;</span><span style="color:#F78C6C;">90</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"><span style="color:#A6ACCD;">#方式2</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees  </span><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> email </span><span style="color:#F78C6C;">LIKE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">%a%</span><span style="color:#89DDFF;">&#39;</span></span>
<span class="line"><span style="color:#F78C6C;">UNION</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees  </span><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> department_id</span><span style="color:#89DDFF;">&gt;</span><span style="color:#F78C6C;">90</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p>举例：查询中国用户中男性的信息以及美国用户中年男性的用户信息</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> id,cname </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> t_chinamale </span><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> csex</span><span style="color:#89DDFF;">=</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">男</span><span style="color:#89DDFF;">&#39;</span></span>
<span class="line"><span style="color:#F78C6C;">UNION ALL</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> id,tname </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> t_usmale </span><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> tGender</span><span style="color:#89DDFF;">=</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">male</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h2 id="_5-7种sql-joins的实现" tabindex="-1">5.7种SQL JOINS的实现 <a class="header-anchor" href="#_5-7种sql-joins的实现" aria-hidden="true">#</a></h2><h3 id="_5-1-图" tabindex="-1">5.1 图 <a class="header-anchor" href="#_5-1-图" aria-hidden="true">#</a></h3><p><img src="https://i0.hdslb.com/bfs/album/85acab011bb121784b5a69f286df9e6449e64437.png" alt=""></p><h3 id="_5-2-代码实现" tabindex="-1">5.2 代码实现 <a class="header-anchor" href="#_5-2-代码实现" aria-hidden="true">#</a></h3><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">#中图：内连接 A∩B</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employee_id,last_name,department_name</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> departments d</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">#左上图：左外连接</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employee_id,last_name,department_name</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span><span style="color:#F78C6C;">LEFT JOIN</span><span style="color:#A6ACCD;"> departments d</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">#右上图：右外连接</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employee_id,last_name,department_name</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span><span style="color:#F78C6C;">RIGHT JOIN</span><span style="color:#A6ACCD;"> departments d</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">#左中图：A </span><span style="color:#89DDFF;">-</span><span style="color:#A6ACCD;"> A∩B</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employee_id,last_name,department_name</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span><span style="color:#F78C6C;">LEFT JOIN</span><span style="color:#A6ACCD;"> departments d</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">IS</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NULL</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">#右中图：B</span><span style="color:#89DDFF;">-</span><span style="color:#A6ACCD;">A∩B</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employee_id,last_name,department_name</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span><span style="color:#F78C6C;">RIGHT JOIN</span><span style="color:#A6ACCD;"> departments d</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">IS</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NULL</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">#左下图：满外连接</span></span>
<span class="line"><span style="color:#A6ACCD;"># 左中图 </span><span style="color:#89DDFF;">+</span><span style="color:#A6ACCD;"> 右上图  A∪B</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employee_id,last_name,department_name</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span><span style="color:#F78C6C;">LEFT JOIN</span><span style="color:#A6ACCD;"> departments d</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">IS</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NULL</span></span>
<span class="line"><span style="color:#F78C6C;">UNION ALL</span><span style="color:#A6ACCD;">  #没有去重操作，效率高</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employee_id,last_name,department_name</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span><span style="color:#F78C6C;">RIGHT JOIN</span><span style="color:#A6ACCD;"> departments d</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">#右下图</span></span>
<span class="line"><span style="color:#A6ACCD;">#左中图 </span><span style="color:#89DDFF;">+</span><span style="color:#A6ACCD;"> 右中图  A ∪B</span><span style="color:#89DDFF;">-</span><span style="color:#A6ACCD;"> A∩B 或者 (A </span><span style="color:#89DDFF;">-</span><span style="color:#A6ACCD;">  A∩B) ∪ （B </span><span style="color:#89DDFF;">-</span><span style="color:#A6ACCD;"> A∩B）</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employee_id,last_name,department_name</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span><span style="color:#F78C6C;">LEFT JOIN</span><span style="color:#A6ACCD;"> departments d</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">IS</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NULL</span></span>
<span class="line"><span style="color:#F78C6C;">UNION ALL</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employee_id,last_name,department_name</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span><span style="color:#F78C6C;">RIGHT JOIN</span><span style="color:#A6ACCD;"> departments d</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">IS</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NULL</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br></div></div><h3 id="_5-3-语法格式小结" tabindex="-1">5.3 语法格式小结 <a class="header-anchor" href="#_5-3-语法格式小结" aria-hidden="true">#</a></h3><ul><li>左中图</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">#实现A </span><span style="color:#89DDFF;">-</span><span style="color:#A6ACCD;">  A∩B</span></span>
<span class="line"><span style="color:#F78C6C;">select</span><span style="color:#A6ACCD;"> 字段列表</span></span>
<span class="line"><span style="color:#F78C6C;">from</span><span style="color:#A6ACCD;"> A表 </span><span style="color:#F78C6C;">left join</span><span style="color:#A6ACCD;"> B表</span></span>
<span class="line"><span style="color:#F78C6C;">on</span><span style="color:#A6ACCD;"> 关联条件</span></span>
<span class="line"><span style="color:#F78C6C;">where</span><span style="color:#A6ACCD;"> 从表关联字段 </span><span style="color:#F78C6C;">is</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">null</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">and</span><span style="color:#A6ACCD;"> 等其他子句;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><ul><li>右中图</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">#实现B </span><span style="color:#89DDFF;">-</span><span style="color:#A6ACCD;">  A∩B</span></span>
<span class="line"><span style="color:#F78C6C;">select</span><span style="color:#A6ACCD;"> 字段列表</span></span>
<span class="line"><span style="color:#F78C6C;">from</span><span style="color:#A6ACCD;"> A表 </span><span style="color:#F78C6C;">right join</span><span style="color:#A6ACCD;"> B表</span></span>
<span class="line"><span style="color:#F78C6C;">on</span><span style="color:#A6ACCD;"> 关联条件</span></span>
<span class="line"><span style="color:#F78C6C;">where</span><span style="color:#A6ACCD;"> 从表关联字段 </span><span style="color:#F78C6C;">is</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">null</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">and</span><span style="color:#A6ACCD;"> 等其他子句;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><ul><li>左下图</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">#实现查询结果是A∪B</span></span>
<span class="line"><span style="color:#A6ACCD;">#用左外的A，</span><span style="color:#F78C6C;">union</span><span style="color:#A6ACCD;"> 右外的B</span></span>
<span class="line"><span style="color:#F78C6C;">select</span><span style="color:#A6ACCD;"> 字段列表</span></span>
<span class="line"><span style="color:#F78C6C;">from</span><span style="color:#A6ACCD;"> A表 </span><span style="color:#F78C6C;">left join</span><span style="color:#A6ACCD;"> B表</span></span>
<span class="line"><span style="color:#F78C6C;">on</span><span style="color:#A6ACCD;"> 关联条件</span></span>
<span class="line"><span style="color:#F78C6C;">where</span><span style="color:#A6ACCD;"> 等其他子句</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">union</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">select</span><span style="color:#A6ACCD;"> 字段列表</span></span>
<span class="line"><span style="color:#F78C6C;">from</span><span style="color:#A6ACCD;"> A表 </span><span style="color:#F78C6C;">right join</span><span style="color:#A6ACCD;"> B表</span></span>
<span class="line"><span style="color:#F78C6C;">on</span><span style="color:#A6ACCD;"> 关联条件</span></span>
<span class="line"><span style="color:#F78C6C;">where</span><span style="color:#A6ACCD;"> 等其他子句;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div><ul><li>右下图</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">#实现A∪B </span><span style="color:#89DDFF;">-</span><span style="color:#A6ACCD;">  A∩B  或   (A </span><span style="color:#89DDFF;">-</span><span style="color:#A6ACCD;">  A∩B) ∪ （B </span><span style="color:#89DDFF;">-</span><span style="color:#A6ACCD;"> A∩B）</span></span>
<span class="line"><span style="color:#A6ACCD;">#使用左外的 (A </span><span style="color:#89DDFF;">-</span><span style="color:#A6ACCD;">  A∩B)  </span><span style="color:#F78C6C;">union</span><span style="color:#A6ACCD;"> 右外的（B </span><span style="color:#89DDFF;">-</span><span style="color:#A6ACCD;"> A∩B）</span></span>
<span class="line"><span style="color:#F78C6C;">select</span><span style="color:#A6ACCD;"> 字段列表</span></span>
<span class="line"><span style="color:#F78C6C;">from</span><span style="color:#A6ACCD;"> A表 </span><span style="color:#F78C6C;">left join</span><span style="color:#A6ACCD;"> B表</span></span>
<span class="line"><span style="color:#F78C6C;">on</span><span style="color:#A6ACCD;"> 关联条件</span></span>
<span class="line"><span style="color:#F78C6C;">where</span><span style="color:#A6ACCD;"> 从表关联字段 </span><span style="color:#F78C6C;">is</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">null</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">and</span><span style="color:#A6ACCD;"> 等其他子句</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">union</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">select</span><span style="color:#A6ACCD;"> 字段列表</span></span>
<span class="line"><span style="color:#F78C6C;">from</span><span style="color:#A6ACCD;"> A表 </span><span style="color:#F78C6C;">right join</span><span style="color:#A6ACCD;"> B表</span></span>
<span class="line"><span style="color:#F78C6C;">on</span><span style="color:#A6ACCD;"> 关联条件</span></span>
<span class="line"><span style="color:#F78C6C;">where</span><span style="color:#A6ACCD;"> 从表关联字段 </span><span style="color:#F78C6C;">is</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">null</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">and</span><span style="color:#A6ACCD;"> 等其他子句</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div><h2 id="_6-sql99语法新特性" tabindex="-1">6.SQL99语法新特性 <a class="header-anchor" href="#_6-sql99语法新特性" aria-hidden="true">#</a></h2><h3 id="_6-1-自然连接" tabindex="-1">6.1 自然连接 <a class="header-anchor" href="#_6-1-自然连接" aria-hidden="true">#</a></h3><p>SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 <code>NATURAL JOIN</code> 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中<code>所有相同的字段</code>，然后进行<code>等值连接</code>。</p><p>在SQL92标准中：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employee_id,last_name,department_name</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> departments d</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span></span>
<span class="line"><span style="color:#F78C6C;">AND</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">manager_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">manager_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>在 SQL99 中你可以写成：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employee_id,last_name,department_name</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span><span style="color:#F78C6C;">NATURAL JOIN</span><span style="color:#A6ACCD;"> departments d;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h3 id="_6-2-using连接" tabindex="-1">6.2 USING连接 <a class="header-anchor" href="#_6-2-using连接" aria-hidden="true">#</a></h3><p>当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的<code>同名字段</code>进行等值连接。但是只能配合JOIN一起使用。比如：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employee_id,last_name,department_name</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> departments d</span></span>
<span class="line"><span style="color:#F78C6C;">USING</span><span style="color:#A6ACCD;"> (department_id);</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING 的括号 () 中填入要指定的同名字段。同时使用 <code>JOIN...USING</code> 可以简化 JOIN ON 的等值连接。它与下面的 SQL 查询结果是相同的：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employee_id,last_name,department_name</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> departments d</span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> e.department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.department_id;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h2 id="_7-章节小结" tabindex="-1">7.章节小结 <a class="header-anchor" href="#_7-章节小结" aria-hidden="true">#</a></h2><p>表连接的约束条件可以有三种方式：WHERE, ON, USING</p><ul><li>WHERE：适用于所有关联查询</li><li><code>ON</code>：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。</li><li>USING：只能和JOIN一起使用，而且要求<strong>两个</strong>关联字段在关联表中名称一致，而且只能表示关联字段值相等</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">#关联条件</span></span>
<span class="line"><span style="color:#A6ACCD;">#把关联条件写在where后面</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name,department_name </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees,departments </span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> employees.department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> departments.department_id;</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">#把关联条件写在on后面，只能和JOIN一起使用</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name,department_name </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees </span><span style="color:#F78C6C;">INNER JOIN</span><span style="color:#A6ACCD;"> departments </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> employees.department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> departments.department_id;</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name,department_name </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees </span><span style="color:#F78C6C;">CROSS</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> departments </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> employees.department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> departments.department_id;</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name,department_name  </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees </span><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> departments </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> employees.department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> departments.department_id;</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">#把关联字段写在using</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">中，只能和JOIN一起使用</span></span>
<span class="line"><span style="color:#A6ACCD;">#而且两个表中的关联字段必须名称相同，而且只能表示</span><span style="color:#89DDFF;">=</span></span>
<span class="line"><span style="color:#A6ACCD;">#查询员工姓名与基本工资</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name,job_title</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees </span><span style="color:#F78C6C;">INNER JOIN</span><span style="color:#A6ACCD;"> jobs </span><span style="color:#F78C6C;">USING</span><span style="color:#A6ACCD;">(job_id);</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">#n张表关联，需要n</span><span style="color:#89DDFF;">-</span><span style="color:#A6ACCD;">1个关联条件</span></span>
<span class="line"><span style="color:#A6ACCD;">#查询员工姓名，基本工资，部门名称</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name,job_title,department_name </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees,departments,jobs </span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> employees.department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> departments.department_id </span></span>
<span class="line"><span style="color:#F78C6C;">AND</span><span style="color:#A6ACCD;"> employees.job_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> jobs.job_id;</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name,job_title,department_name </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees </span><span style="color:#F78C6C;">INNER JOIN</span><span style="color:#A6ACCD;"> departments </span><span style="color:#F78C6C;">INNER JOIN</span><span style="color:#A6ACCD;"> jobs </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> employees.department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> departments.department_id </span></span>
<span class="line"><span style="color:#F78C6C;">AND</span><span style="color:#A6ACCD;"> employees.job_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> jobs.job_id;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br></div></div><p><strong>注意：</strong></p><p>我们要<code>控制连接表的数量</code>。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。</p><blockquote><p>【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。</p><p>说明：即使双表 join 也要注意表索引、SQL 性能。</p><p>来源：阿里巴巴《Java开发手册》</p></blockquote><h3 id="附录：常用的-sql-标准有哪些" tabindex="-1">附录：常用的 SQL 标准有哪些 <a class="header-anchor" href="#附录：常用的-sql-标准有哪些" aria-hidden="true">#</a></h3><p>在正式开始讲连接表的种类时，我们首先需要知道 SQL 存在不同版本的标准规范，因为不同规范下的表连接操作是有区别的。</p><p>SQL 有两个主要的标准，分别是 <code>SQL92</code> 和 <code>SQL99</code>。92 和 99 代表了标准提出的时间，SQL92 就是 92 年提出的标准规范。当然除了 SQL92 和 SQL99 以外，还存在 SQL-86、SQL-89、SQL:2003、SQL:2008、SQL:2011 和 SQL:2016 等其他的标准。</p><p>这么多标准，到底该学习哪个呢？<strong><code>实际上最重要的 SQL 标准就是 SQL92 和 SQL99</code></strong>。一般来说 SQL92 的形式更简单，但是写的 SQL 语句会比较长，可读性较差。而 SQL99 相比于 SQL92 来说，语法更加复杂，但可读性更强。我们从这两个标准发布的页数也能看出，SQL92 的标准有 500 页，而 SQL99 标准超过了 1000 页。实际上从 SQL99 之后，很少有人能掌握所有内容，因为确实太多了。就好比我们使用 Windows、Linux 和 Office 的时候，很少有人能掌握全部内容一样。我们只需要掌握一些核心的功能，满足日常工作的需求即可。</p><p>**<code>SQL92 和 SQL99 是经典的 SQL 标准，也分别叫做 SQL-2 和 SQL-3 标准。</code>**也正是在这两个标准发布之后，SQL 影响力越来越大，甚至超越了数据库领域。现如今 SQL 已经不仅仅是数据库领域的主流语言，还是信息领域中信息处理的主流语言。在图形检索、图像检索以及语音检索中都能看到 SQL 语言的使用。</p><h2 id="_8-练习" tabindex="-1">8.练习 <a class="header-anchor" href="#_8-练习" aria-hidden="true">#</a></h2><h3 id="_8-1-多表查询1" tabindex="-1">8.1 多表查询1 <a class="header-anchor" href="#_8-1-多表查询1" aria-hidden="true">#</a></h3><p>1.显示所有员工的姓名，部门号和部门名称</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name, e.department_id, department_name </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span></span>
<span class="line"><span style="color:#F78C6C;">LEFT OUTER JOIN</span><span style="color:#A6ACCD;"> departments d </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>2.查询90号部门员工的job_id和90号部门的location_id</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> job_id, location_id </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e, departments d </span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">AND</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">90</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">------------或----------</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> job_id, location_id </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span></span>
<span class="line"><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> departments d </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">90</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br></div></div><p>3.选择所有有奖金的员工的 last_name , department_name , location_id , city</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name , department_name , d.location_id , city </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span><span style="color:#82AAFF;">LEFT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">OUTER</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> departments d </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">LEFT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">OUTER</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> locations l </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">location_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> l.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">location_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> commission_pct </span><span style="color:#F78C6C;">IS</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NOT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NULL</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><p>4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name , job_id , e.department_id , department_name </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e, departments d, locations l </span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">AND</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">location_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> l.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">location_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">AND</span><span style="color:#A6ACCD;"> city </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">Toronto</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">------------或--------------</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name , job_id , e.department_id , department_name </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span></span>
<span class="line"><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> departments d </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> locations l </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> l.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">location_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">location_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> l.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">city</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">Toronto</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br></div></div><p>5．查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所 在部门的部门名称为’Executive’</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> department_name, street_address, last_name, job_id, salary </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span></span>
<span class="line"><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> departments d </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> e.department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.department_id </span></span>
<span class="line"><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> locations l </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">location_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> l.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">location_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> department_name </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">Executive</span><span style="color:#89DDFF;">&#39;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><p>6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果 类似于下面的格式</p><p><img src="https://img2022.cnblogs.com/blog/2402456/202206/2402456-20220611182821793-1656692981.png" alt=""></p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> emp.last_name employees, emp.employee_id </span><span style="color:#89DDFF;">&quot;</span><span style="color:#C3E88D;">Emp#</span><span style="color:#89DDFF;">&quot;</span><span style="color:#A6ACCD;">, mgr.last_name manager, mgr.employee_id </span><span style="color:#89DDFF;">&quot;</span><span style="color:#C3E88D;">Mgr#</span><span style="color:#89DDFF;">&quot;</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees emp </span><span style="color:#82AAFF;">LEFT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">OUTER</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> employees mgr </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> emp.manager_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> mgr.employee_id;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>7.查询哪些部门没有员工</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;"># 方式1： </span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> d.department_id </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> departments d </span><span style="color:#82AAFF;">LEFT</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">JOIN</span><span style="color:#A6ACCD;"> employees e </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> e.department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> e.department_id </span><span style="color:#F78C6C;">IS</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NULL</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">#方式2： </span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> department_id </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> departments d </span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NOT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">EXISTS</span><span style="color:#A6ACCD;"> ( </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e </span><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> e.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> )</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br></div></div><p>8.查询哪个城市没有部门</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> l.location_id,l.city </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> locations l </span><span style="color:#F78C6C;">LEFT JOIN</span><span style="color:#A6ACCD;"> departments d </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> l.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">location_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">location_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">location_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">IS</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NULL</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>9.查询部门名为 Sales 或 IT 的员工信息</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employee_id,last_name,department_name </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees e,departments d </span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> e.department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">AND</span><span style="color:#A6ACCD;"> d.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">department_name</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">IN</span><span style="color:#A6ACCD;"> (</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">Sales</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">IT</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h3 id="_8-2-多表查询2" tabindex="-1">8.2 多表查询2 <a class="header-anchor" href="#_8-2-多表查询2" aria-hidden="true">#</a></h3><p>建表：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#676E95;font-style:italic;">-- 储备：建表操作： </span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">t_dept</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> ( </span></span>
<span class="line"><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INT</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">11</span><span style="color:#A6ACCD;">) </span><span style="color:#F78C6C;">NOT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NULL</span><span style="color:#A6ACCD;"> AUTO_INCREMENT, </span></span>
<span class="line"><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">deptName</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">VARCHAR</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">30</span><span style="color:#A6ACCD;">) </span><span style="color:#C792EA;">DEFAULT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NULL</span><span style="color:#A6ACCD;">, </span></span>
<span class="line"><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">address</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">VARCHAR</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">40</span><span style="color:#A6ACCD;">) </span><span style="color:#C792EA;">DEFAULT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NULL</span><span style="color:#A6ACCD;">, </span></span>
<span class="line"><span style="color:#F78C6C;">PRIMARY</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">KEY</span><span style="color:#A6ACCD;"> (</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;">) </span></span>
<span class="line"><span style="color:#A6ACCD;">) ENGINE</span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;">INNODB AUTO_INCREMENT</span><span style="color:#89DDFF;">=</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#C792EA;">DEFAULT</span><span style="color:#A6ACCD;"> CHARSET</span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;">utf8; </span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">t_emp</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> ( </span></span>
<span class="line"><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INT</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">11</span><span style="color:#A6ACCD;">) </span><span style="color:#F78C6C;">NOT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NULL</span><span style="color:#A6ACCD;"> AUTO_INCREMENT, </span></span>
<span class="line"><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">name</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">VARCHAR</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">20</span><span style="color:#A6ACCD;">) </span><span style="color:#C792EA;">DEFAULT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NULL</span><span style="color:#A6ACCD;">, </span></span>
<span class="line"><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">age</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INT</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">3</span><span style="color:#A6ACCD;">) </span><span style="color:#C792EA;">DEFAULT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NULL</span><span style="color:#A6ACCD;">, </span></span>
<span class="line"><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">deptId</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INT</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">11</span><span style="color:#A6ACCD;">) </span><span style="color:#C792EA;">DEFAULT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NULL</span><span style="color:#A6ACCD;">, </span></span>
<span class="line"><span style="color:#A6ACCD;">empno </span><span style="color:#F78C6C;">int</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">not</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">null</span><span style="color:#A6ACCD;">, </span></span>
<span class="line"><span style="color:#F78C6C;">PRIMARY</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">KEY</span><span style="color:#A6ACCD;"> (</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;">), </span></span>
<span class="line"><span style="color:#F78C6C;">KEY</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">idx_dept_id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> (</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">deptId</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;">) </span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">-- CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`) </span></span>
<span class="line"><span style="color:#A6ACCD;">) ENGINE</span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;">INNODB AUTO_INCREMENT</span><span style="color:#89DDFF;">=</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#C792EA;">DEFAULT</span><span style="color:#A6ACCD;"> CHARSET</span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;">utf8;</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">-- 插入数据</span></span>
<span class="line"><span style="color:#F78C6C;">INSERT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INTO</span><span style="color:#A6ACCD;"> t_dept(deptName,</span><span style="color:#F78C6C;">address</span><span style="color:#A6ACCD;">) </span><span style="color:#F78C6C;">VALUES</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">华山</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">华山</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#F78C6C;">INSERT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INTO</span><span style="color:#A6ACCD;"> t_dept(deptName,</span><span style="color:#F78C6C;">address</span><span style="color:#A6ACCD;">) </span><span style="color:#F78C6C;">VALUES</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">丐帮</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">洛阳</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#F78C6C;">INSERT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INTO</span><span style="color:#A6ACCD;"> t_dept(deptName,</span><span style="color:#F78C6C;">address</span><span style="color:#A6ACCD;">) </span><span style="color:#F78C6C;">VALUES</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">峨眉</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">峨眉山</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#F78C6C;">INSERT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INTO</span><span style="color:#A6ACCD;"> t_dept(deptName,</span><span style="color:#F78C6C;">address</span><span style="color:#A6ACCD;">) </span><span style="color:#F78C6C;">VALUES</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">武当</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">武当山</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#F78C6C;">INSERT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INTO</span><span style="color:#A6ACCD;"> t_dept(deptName,</span><span style="color:#F78C6C;">address</span><span style="color:#A6ACCD;">) </span><span style="color:#F78C6C;">VALUES</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">明教</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">光明顶</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#F78C6C;">INSERT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INTO</span><span style="color:#A6ACCD;"> t_dept(deptName,</span><span style="color:#F78C6C;">address</span><span style="color:#A6ACCD;">) </span><span style="color:#F78C6C;">VALUES</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">少林</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">少林寺</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#F78C6C;">INSERT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INTO</span><span style="color:#A6ACCD;"> t_emp(</span><span style="color:#F78C6C;">NAME</span><span style="color:#A6ACCD;">,age,deptId,empno) </span><span style="color:#F78C6C;">VALUES</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">风清扬</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">90</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">100001</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#F78C6C;">INSERT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INTO</span><span style="color:#A6ACCD;"> t_emp(</span><span style="color:#F78C6C;">NAME</span><span style="color:#A6ACCD;">,age,deptId,empno) </span><span style="color:#F78C6C;">VALUES</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">岳不群</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">50</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">100002</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#F78C6C;">INSERT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INTO</span><span style="color:#A6ACCD;"> t_emp(</span><span style="color:#F78C6C;">NAME</span><span style="color:#A6ACCD;">,age,deptId,empno) </span><span style="color:#F78C6C;">VALUES</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">令狐冲</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">24</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">100003</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#F78C6C;">INSERT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INTO</span><span style="color:#A6ACCD;"> t_emp(</span><span style="color:#F78C6C;">NAME</span><span style="color:#A6ACCD;">,age,deptId,empno) </span><span style="color:#F78C6C;">VALUES</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">洪七公</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">70</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">2</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">100004</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#F78C6C;">INSERT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INTO</span><span style="color:#A6ACCD;"> t_emp(</span><span style="color:#F78C6C;">NAME</span><span style="color:#A6ACCD;">,age,deptId,empno) </span><span style="color:#F78C6C;">VALUES</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">乔峰</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">35</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">2</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">100005</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#F78C6C;">INSERT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INTO</span><span style="color:#A6ACCD;"> t_emp(</span><span style="color:#F78C6C;">NAME</span><span style="color:#A6ACCD;">,age,deptId,empno) </span><span style="color:#F78C6C;">VALUES</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">灭绝师太</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">70</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">3</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">100006</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#F78C6C;">INSERT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INTO</span><span style="color:#A6ACCD;"> t_emp(</span><span style="color:#F78C6C;">NAME</span><span style="color:#A6ACCD;">,age,deptId,empno) </span><span style="color:#F78C6C;">VALUES</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">周芷若</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">20</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">3</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">100007</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#F78C6C;">INSERT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INTO</span><span style="color:#A6ACCD;"> t_emp(</span><span style="color:#F78C6C;">NAME</span><span style="color:#A6ACCD;">,age,deptId,empno) </span><span style="color:#F78C6C;">VALUES</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">张三丰</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">100</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">4</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">100008</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#F78C6C;">INSERT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INTO</span><span style="color:#A6ACCD;"> t_emp(</span><span style="color:#F78C6C;">NAME</span><span style="color:#A6ACCD;">,age,deptId,empno) </span><span style="color:#F78C6C;">VALUES</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">张无忌</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">25</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">5</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">100009</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#F78C6C;">INSERT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">INTO</span><span style="color:#A6ACCD;"> t_emp(</span><span style="color:#F78C6C;">NAME</span><span style="color:#A6ACCD;">,age,deptId,empno) </span><span style="color:#F78C6C;">VALUES</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">韦小宝</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">18</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">null</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">100010</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br></div></div><p><strong>1.</strong> <strong>所有有门派的人员信息</strong> （ A、B两表共有）</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">select</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">from</span><span style="color:#A6ACCD;"> t_emp a </span><span style="color:#F78C6C;">inner join</span><span style="color:#A6ACCD;"> t_dept b </span><span style="color:#F78C6C;">on</span><span style="color:#A6ACCD;"> a.deptId </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> b.id;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><strong>2.</strong> <strong>列出所有用户，并显示其机构信息</strong> （A的全集）</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">select</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">from</span><span style="color:#A6ACCD;"> t_emp a </span><span style="color:#F78C6C;">left join</span><span style="color:#A6ACCD;"> t_dept b </span><span style="color:#F78C6C;">on</span><span style="color:#A6ACCD;"> a.deptId </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> b.id;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><strong>3.</strong> <strong>列出所有门派</strong> （B的全集）</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">select</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">from</span><span style="color:#A6ACCD;"> t_dept b;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><strong>4.</strong> <strong>所有不入门派的人员</strong> （A的独有）</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">select</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span></span>
<span class="line"><span style="color:#F78C6C;">from</span><span style="color:#A6ACCD;"> t_emp a </span><span style="color:#82AAFF;">left</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">join</span><span style="color:#A6ACCD;"> t_dept b </span></span>
<span class="line"><span style="color:#F78C6C;">on</span><span style="color:#A6ACCD;"> a.deptId </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> b.id </span></span>
<span class="line"><span style="color:#F78C6C;">where</span><span style="color:#A6ACCD;"> b.id </span><span style="color:#F78C6C;">is</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">null</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p><strong>5.</strong> <strong>所有没人入的门派</strong> （B的独有）</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">select</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span></span>
<span class="line"><span style="color:#F78C6C;">from</span><span style="color:#A6ACCD;"> t_dept b </span><span style="color:#82AAFF;">left</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">join</span><span style="color:#A6ACCD;"> t_emp a </span></span>
<span class="line"><span style="color:#F78C6C;">on</span><span style="color:#A6ACCD;"> a.deptId </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> b.id </span></span>
<span class="line"><span style="color:#F78C6C;">where</span><span style="color:#A6ACCD;"> a.deptId </span><span style="color:#F78C6C;">is</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">null</span><span style="color:#A6ACCD;">; </span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p><strong>6.</strong> <strong>列出所有人员和机构的对照关系</strong> (AB全有)</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#676E95;font-style:italic;">-- MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法 </span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">-- left join + union(可去除重复数据)+ right join </span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> t_emp A </span><span style="color:#F78C6C;">LEFT JOIN</span><span style="color:#A6ACCD;"> t_dept B </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> A.deptId </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> B.id </span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">UNION</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> t_emp A </span><span style="color:#F78C6C;">RIGHT JOIN</span><span style="color:#A6ACCD;"> t_dept B </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> A.deptId </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> B.id</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div><p><strong>7.</strong> <strong>列出所有没入派的人员和没人入的门派</strong> （A的独有+B的独有）</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> t_emp A </span><span style="color:#F78C6C;">LEFT JOIN</span><span style="color:#A6ACCD;"> t_dept B </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> A.deptId </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> B.id </span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> B.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">id</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">IS</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NULL</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">UNION</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> t_emp A </span><span style="color:#F78C6C;">RIGHT JOIN</span><span style="color:#A6ACCD;"> t_dept B </span></span>
<span class="line"><span style="color:#F78C6C;">ON</span><span style="color:#A6ACCD;"> A.deptId </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> B.id </span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> A.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">deptId</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">IS</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NULL</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br></div></div></div></div><!----></main><!--[--><!--]--><footer class="VPContentDocFooter" data-v-4fe9b7bd data-v-7f892bef><a class="prev-link" href="/mysql/05.html" data-v-7f892bef><span class="desc" data-v-7f892bef><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-link-icon" data-v-7f892bef><path d="M15,19c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4l6-6c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4L10.4,12l5.3,5.3c0.4,0.4,0.4,1,0,1.4C15.5,18.9,15.3,19,15,19z"></path></svg> Previous</span><span class="title" data-v-7f892bef>05 【排序与分页】</span></a><a class="next-link" href="/mysql/07.html" data-v-7f892bef><span class="desc" data-v-7f892bef>Next <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-link-icon" data-v-7f892bef><path d="M9,19c-0.3,0-0.5-0.1-0.7-0.3c-0.4-0.4-0.4-1,0-1.4l5.3-5.3L8.3,6.7c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l6,6c0.4,0.4,0.4,1,0,1.4l-6,6C9.5,18.9,9.3,19,9,19z"></path></svg></span><span class="title" data-v-7f892bef>07 【单行函数】</span></a></footer></div></div></div></div><div class="visually-hidden" aria-live="polite" data-v-23306c50>06 【多表查询】 has loaded</div></div></div>
    <script>__VP_HASH_MAP__ = JSON.parse("{\"about_canshu.md\":\"5cbc0865\",\"about_chahuo.md\":\"4437b8d9\",\"about_dayin.md\":\"34b5b775\",\"ajax_01.md\":\"2bc3ac36\",\"ajax_02.md\":\"2b66aa43\",\"echarts_01.md\":\"8784be9b\",\"echarts_02.md\":\"3e6ecdea\",\"echarts_03.md\":\"28ca81f0\",\"echarts_04.md\":\"d02d74fb\",\"echarts_05.md\":\"f150b054\",\"es6_01.md\":\"53b72f76\",\"es6_02.md\":\"1fc3a71b\",\"es6_03.md\":\"2b8d77d0\",\"es6_04.md\":\"19f87206\",\"es6_05.md\":\"78caf034\",\"es6_06.md\":\"f8d3c991\",\"es6_07.md\":\"324f9949\",\"es6_08.md\":\"87411498\",\"es6_09.md\":\"3486bc74\",\"es6_10.md\":\"71a60a12\",\"es6_11.md\":\"6d589e6c\",\"es6_12.md\":\"d82e096b\",\"es6_13.md\":\"78856900\",\"es6_14.md\":\"40c129d1\",\"git_01.md\":\"bf73bb28\",\"git_02.md\":\"650fa8d0\",\"git_03.md\":\"4384f718\",\"git_04.md\":\"14478c6d\",\"index.md\":\"09348a64\",\"jihe_react_index.md\":\"f57878ae\",\"jihe_reactnative_index.md\":\"c7e0faf3\",\"jihe_vue_index.md\":\"990e53d2\",\"jihe_vuerouter_index.md\":\"d28167d1\",\"jihe_vuex_index.md\":\"b9d762ab\",\"js_01.md\":\"48c6f3a8\",\"js_02.md\":\"a5183efb\",\"js_03.md\":\"5a8ce924\",\"js_04.md\":\"3db3e706\",\"js_05.md\":\"7b8951d8\",\"js_06.md\":\"dc3d579a\",\"js_07.md\":\"e7a4eda3\",\"js_08.md\":\"f75c92a3\",\"js_09.md\":\"80e86e46\",\"js_10.md\":\"646ac118\",\"js_11.md\":\"7d754050\",\"js_12.md\":\"7e6eae06\",\"js_13.md\":\"947200bc\",\"js_14.md\":\"1c0566af\",\"js_15.md\":\"d465d165\",\"js_16.md\":\"1c7c9370\",\"js_17.md\":\"71dec812\",\"js_18.md\":\"d68348c2\",\"js_19.md\":\"72c34e28\",\"js_20.md\":\"ff30fdd3\",\"js_21.md\":\"aa9bea2b\",\"js_22.md\":\"6bc45920\",\"js_23.md\":\"3131d6e6\",\"js_24.md\":\"5f12ad69\",\"js_25.md\":\"c5932cf5\",\"js_26.md\":\"b8e719df\",\"js_27.md\":\"6679728d\",\"js_28.md\":\"c9ec23e0\",\"js_29.md\":\"09376a4e\",\"js_30.md\":\"dbc798cb\",\"js_31.md\":\"283dae59\",\"js_32.md\":\"11d6ad8d\",\"js_33.md\":\"e4232cd9\",\"js_34.md\":\"a3b52a45\",\"js_35.md\":\"1477beb5\",\"js_36.md\":\"01702bb7\",\"keshitong_caigou.md\":\"c5f9edad\",\"keshitong_caiwu.md\":\"90dbd259\",\"keshitong_index.md\":\"7522f84e\",\"keshitong_xiaoshou.md\":\"b4d67695\",\"keshitong_xunjia.md\":\"49b056fe\",\"less_01.md\":\"cc6e26b8\",\"linux_01.md\":\"40a56533\",\"linux_02.md\":\"fa080f7e\",\"linux_03.md\":\"9c723c17\",\"linux_04.md\":\"c4b01f09\",\"linux_05.md\":\"cfe63746\",\"linux_06.md\":\"4e05c7c6\",\"linux_07.md\":\"2dbdda0c\",\"linux_08.md\":\"80f78478\",\"linux_09.md\":\"b9157005\",\"linux_10.md\":\"55e60bfe\",\"linux_11.md\":\"3b59a057\",\"mongodb_01.md\":\"4e9aab55\",\"mysql_01.md\":\"66f81032\",\"mysql_02.md\":\"38ff9d8a\",\"mysql_03.md\":\"4a1047e3\",\"mysql_04.md\":\"ed6dc0f6\",\"mysql_05.md\":\"fb50b511\",\"mysql_06.md\":\"4f4b1170\",\"mysql_07.md\":\"3f58b4d2\",\"mysql_08.md\":\"7e23f325\",\"mysql_09.md\":\"bef84df5\",\"mysql_10.md\":\"33e4e499\",\"mysql_11.md\":\"4e732a7d\",\"nodejs_01.md\":\"614d43a3\",\"nodejs_02.md\":\"4899f883\",\"nodejs_03.md\":\"f47d5f16\",\"nodejs_04.md\":\"7755e031\",\"nodejs_05.md\":\"ef947c59\",\"nodejs_06.md\":\"ab0a9b5a\",\"nodejs_07.md\":\"ad0e80be\",\"nodejs_08.md\":\"11027bdf\",\"nodejs_09.md\":\"4e4f9b03\",\"nodejs_10.md\":\"c4453555\",\"nodejs_11.md\":\"9f62715b\",\"nodejs_12.md\":\"1229399a\",\"nodejs_13.md\":\"e490f00c\",\"nodejs_14.md\":\"d756be55\",\"nodejs_15.md\":\"eb622fe8\",\"nodejs_16.md\":\"a5beb6c6\",\"sass_01.md\":\"0d150b1a\",\"sass_02.md\":\"8ee85f45\",\"sass_03.md\":\"7c4069d6\",\"sass_04.md\":\"85602152\",\"sass_05.md\":\"b62c38b1\",\"sass_06.md\":\"d02e3012\",\"sass_07.md\":\"edcc3b93\",\"sass_08.md\":\"9c108ef4\",\"sass_09.md\":\"3bdde859\",\"sass_10.md\":\"7eac5fed\",\"sass_11.md\":\"3d1861cf\",\"tailwindcss_01.md\":\"de90a25d\",\"tailwindcss_02.md\":\"bbfd989e\",\"tailwindcss_03.md\":\"dc351bec\",\"tailwindcss_04.md\":\"27601329\",\"test_index.md\":\"57ee7ee1\",\"test_muban.md\":\"5118e1bd\",\"test_zhongqiu.md\":\"3d9111e0\",\"typescript_01.md\":\"27d9f7e0\",\"typescript_02.md\":\"6cf9cb32\",\"typescript_03.md\":\"a851d1d2\",\"typescript_04.md\":\"954cb0d0\",\"typescript_05.md\":\"989bd8d1\",\"typescript_06.md\":\"2c90dcac\",\"vue2_01.md\":\"fd17f4d6\",\"vue2_02.md\":\"84ab761d\",\"vue2_03.md\":\"a64353d4\",\"vue2_04.md\":\"b9b0b23e\",\"vue2_05.md\":\"81a8a08d\",\"vue2_06.md\":\"2ce32e2a\",\"vue2_07.md\":\"19b50842\",\"vue2_08.md\":\"4c8868f5\",\"vue2_09.md\":\"94977e11\",\"vue2_10.md\":\"8e789ccc\",\"vue2_11.md\":\"ee9e7943\",\"vue2_12.md\":\"5249d4c5\",\"vue2_13.md\":\"048d4471\",\"vue2_14.md\":\"63be0ece\",\"vue2_15.md\":\"bd679101\",\"vue2_16.md\":\"bbe6e777\",\"vue3_01.md\":\"27d0d58b\",\"vue3_02.md\":\"22bcb9e5\",\"vue3_03.md\":\"3c52430b\",\"vue3_04.md\":\"0bb848ba\",\"vue3_06.md\":\"1cc04cca\",\"vue3_07.md\":\"28531efb\",\"vue3_08.md\":\"66ee6b02\",\"vue3_09.md\":\"9ea9285a\",\"vue3_10.md\":\"a1d6a99a\",\"vue3_11.md\":\"cb4aa0f5\",\"vue3_12.md\":\"27e13ddf\",\"vue3_13.md\":\"3976cde5\",\"vue3_14.md\":\"1b2e16e7\",\"vue3_15.md\":\"24dc5c2d\",\"vue3_16.md\":\"5efc1ed6\",\"vue3_17.md\":\"ac67e2ed\",\"yuanshen_index.md\":\"437ce691\"}")</script>
    <script type="module" async src="/assets/app.82d46cfc.js"></script>
    
  </body>
</html>